﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using Framework;

namespace Enterprise
{
    public class PropertyService : BaseService
    {
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(int pageValue = 0, int rowsValue = 0, string sort = "CoDate", string order = "Desc",
                              string field = "", string language = "")
        {
            var parameters = new[]
                {
                    new OleDbParameter("@CoField", OleDbType.VarChar, 255),
                      new OleDbParameter("@CoLanguage", OleDbType.VarChar, 10)
                 
                };
            parameters[0].Value = field;
            parameters[1].Value = language;
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where CoField=@CoField and CoLanguage=@CoLanguage ");

            int total = DbHelperOleDb.GetSingle("select count(*) from TbProperty {0} ".FormatWith(sbSqlWhere), parameters).SafeToInt();
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} * from TbProperty  {1} ", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(" and CoPropertyId not in (select top {0} CoPropertyId from TbProperty {1} order by {2} {3},CoPropertyId desc) ", rowsValue * (pageValue - 1), sbSqlWhere, sort, order);
            sbSql.AppendFormat(" order by {0} {1},CoPropertyId desc", sort, order);
            var ds = DbHelperOleDb.Query(sbSql.ToString(), parameters);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total
            };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperOleDb.Query("select * from TbProperty where CoPropertyId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="field"></param>
        /// <param name="language"> </param>
        /// <returns></returns>
        public DataRow GetDataRow(string field, string language)
        {
            OleDbParameter[] parameters =
                {
                    new OleDbParameter("@CoField", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoLanguage", OleDbType.VarChar, 10)
                };
            parameters[0].Value = field;
            parameters[1].Value = language;
            return DbHelperOleDb.Query("select * from TbProperty where CoLanguage=@CoLanguage and CoField=@CoField", parameters).Tables["ds"].Rows[0];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="title"></param>
        /// <param name="keyword"></param>
        /// <param name="descirption"></param>
        /// <param name="author"></param>
        /// <param name="field"></param>
        /// <param name="name"></param>
        /// <param name="language"></param>
        /// <param name="dataType"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public int Add(string title, string keyword, string descirption, string author, string field, string name, string language, string dataType, string content)
        {

            var sbSql = new StringBuilder();
            sbSql.Append("insert into TbProperty(");
            sbSql.Append("CoTitle,CoKeyword,CoDescirption,CoAuthor,CoField,CoDataType,CoName,CoLanguage,CoDate,CoContent");
            sbSql.Append(")");
            sbSql.Append(" values (");
            sbSql.Append("@CoTitle,@CoKeyword,@CoDescirption,@CoAuthor,@CoField,@CoDataType,@CoName,@CoLanguage,@CoDate,@CoContent");
            sbSql.Append(")");
            OleDbParameter[] parameters =
                {
                    new OleDbParameter("@CoTitle", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoKeyword", OleDbType.VarChar,255),
                    new OleDbParameter("@CoDescirption", OleDbType.VarChar,255),
                    new OleDbParameter("@CoAuthor", OleDbType.VarChar,50),
                    new OleDbParameter("@CoField", OleDbType.VarChar,255),
                    new OleDbParameter("@CoDataType", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoName", OleDbType.VarChar,255),
                    new OleDbParameter("@CoLanguage", OleDbType.VarChar, 10),
                    new OleDbParameter("@CoDate", OleDbType.Date),
                    new OleDbParameter("@CoContent", OleDbType.LongVarWChar)
                };
            parameters[0].Value = title;
            parameters[1].Value = keyword;
            parameters[2].Value = descirption;
            parameters[3].Value = author;
            parameters[4].Value = field;
            parameters[5].Value = dataType;
            parameters[6].Value = name;
            parameters[7].Value = language;
            parameters[8].Value = DateTime.Now;
            parameters[9].Value = content;
            var obj = DbHelperOleDb.GetSingle(sbSql.ToString(), parameters).SafeToInt();
            return obj;

        }
        public void Edit(int id, string title, string keyword, string descirption, string author, string field, string name, string language, string dataType, string content)
        {
            var strSql = new StringBuilder();
            strSql.Append(" update TbProperty set ");
            strSql.Append(" CoTitle=@CoTitle,");
            strSql.Append(" CoKeyword=@CoKeyword,");
            strSql.Append(" CoDescirption=@CoDescirption,");
            strSql.Append(" CoAuthor=@CoAuthor,");
            strSql.Append(" CoField=@CoField,");
            strSql.Append(" CoDataType=@CoDataType,");
            strSql.Append(" CoName=@CoName,");
            strSql.Append(" CoLanguage=@CoLanguage,");
            strSql.Append(" CoContent=@CoContent ");
            strSql.AppendFormat(" where CoPropertyId={0} ", id);
            OleDbParameter[] parameters =
                {
                    new OleDbParameter("@CoTitle", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoKeyword", OleDbType.VarChar,255),
                    new OleDbParameter("@CoDescirption", OleDbType.VarChar,255),
                    new OleDbParameter("@CoAuthor", OleDbType.VarChar,50),
                    new OleDbParameter("@CoField", OleDbType.VarChar,255),
                    new OleDbParameter("@CoDataType", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoName", OleDbType.VarChar,255),
                    new OleDbParameter("@CoLanguage", OleDbType.VarChar, 10),
                    new OleDbParameter("@CoContent", OleDbType.LongVarWChar)
                };
            parameters[0].Value = title;
            parameters[1].Value = keyword;
            parameters[2].Value = descirption;
            parameters[3].Value = author;
            parameters[4].Value = field;
            parameters[5].Value = dataType;
            parameters[6].Value = name;
            parameters[7].Value = language;
            parameters[8].Value = content;
            var obj = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters).SafeToInt();
            if (!(obj > 0))
            {
                throw new Exception("记录不存在！");
            }
        }
        public void Delete(int id)
        {
            var obj = DbHelperOleDb.ExecuteSql("delete from TbProperty  where CoPropertyId={0}".FormatWith(id)).SafeToInt();
            if (!(obj > 0))
            {
                throw new Exception("记录不存在！");
            }
        }

    }
}
